#! /usr/bin/python
#coding=utf-8
from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.sql import text
import csv,sys,re
reload(sys)
sys.setdefaultencoding('utf-8')

class Category(object):
    pass
    # __name__='category'
    # id=Column(Integer, primary_key=True)
    # name=Column(CHAR(255))
    # url=Column(CHAR(255))
    # title=Column(CHAR(255))
    # keywords=Column(CHAR(255))
    # description=Column(Text)
    # desc=Column(Text)
    # url_code=Column(Unicode(255))
    # level=Column(Unicode(255))

class Top(object):
    pass
    # __name__='top'
    # id=Column(Integer, primary_key=True)
    # name=Column(CHAR(255))
    # url_code=Column(Unicode(255))
    # url=Column(Unicode(255))
    # cat_code=Column(Unicode(255))
    # order=Column(Integer)
    # focus_name=Column(Unicode(255))

class Product(object):
    pass
    # __name__='product'
    # id=Column(Integer, primary_key=True)
    # name=Column(CHAR(255),nullable=False)
    # url_code=Column(Unicode(255))
    # url=Column(Unicode(255))
    # image=Column(Unicode(255))
    # price=Column(Unicode(255))
    # cat_code=Column(Unicode(255))
    # page=Column(Integer)

class ProductExt(Product):
    pass
    # __name__='product_ext'
    # id=Column(Integer, primary_key=True)
    # product_id=Column(Integer,ForeignKey('product.id'))
    # image=Column(Text)
    # summary=Column(Text)
    # description_m=Column(Text)

class Image:
    def __init__(self):
        self.engine =create_engine('mysql://root:123456@localhost/tomtop?charset=utf8',echo=True)
        # self.engine =create_engine('mysql://focal:focalweiming@localhost/focal?charset=utf8',echo=True)
        metadata = MetaData()
        category_table=Table('category',metadata,
            Column('id',Integer, primary_key=True),
            Column('name',CHAR(255)),
            Column('reid',Integer),
            Column('pid',CHAR(255)),
            Column('url',CHAR(255)),
            Column('title',CHAR(255)),
            Column('keywords',CHAR(255)),
            Column('description',Text),
            Column('desc',Text),
            Column('url_code',Unicode(255)),
            Column('level',Unicode(255)),
        )
        top_table=Table('top',metadata,
            Column('id',Integer, primary_key=True),
            Column('name',CHAR(255)),
            Column('url_code',Unicode(255)),
            Column('url',Unicode(255)),
            Column('cat_code',Unicode(255)),
            Column('order',Integer),
            Column('focus_name',Unicode(255)),
        )
        product_table=Table('product',metadata,
            Column('id',Integer,nullable=False, primary_key=True),
            Column('name',CHAR(255)),
            Column('url_code',Unicode(255)),
            Column('url',Unicode(255)),
            Column('cat_code',Unicode(255)),
            Column('image',Unicode(255)),
            Column('price',Unicode(255)),
            Column('page',Integer),
        )
        productext_table=Table('product_ext',metadata,
            Column('id',Integer, primary_key=True),
            Column('product_id',Integer,ForeignKey('product.id')),
            Column('images',Text),
            Column('summary',Text),
            Column('description_m',Text),
        )
        metadata.create_all(self.engine)
        mapper(Category,category_table)
        mapper(Top,top_table)
        mapper(Product,product_table)
        mapper(ProductExt,productext_table,inherits=Product)
        Session = sessionmaker()
        Session.configure(bind=self.engine)
        self.db = Session()

    def product(self):
        print "Select product list is all"
        #q=self.db.query(Product).all()
        #4084
        q=self.db.query(ProductExt)#.filter(ProductExt.id <16801)
        i=1
        for r in q:
            imgs=r.images.strip(',').rsplit(',')
            for img in imgs:
                print img
                #http://img2.tomtop.com/media/catalog/product/cache/1/image/400x400/ced77cb19565515451b3578a3bc0ea5e/c/1/c1916-3-3acb_1.jpg
                #http://img2.tomtop.com/media/catalog/product/c/1/c1916-3-3acb_1.jpg
                og=re.compile('cache\/\d+\/image\/\w+\/\w+\/',re.I)
                img=og.sub('',img)
                print img
                exit()
            exit()



    def csv(self):
        csvfile = file('csv16800.csv', 'wb')
        writer = csv.writer(csvfile)
        writer.writerow([
           'v_products_model',
           'v_products_image',
           'v_products_name_1',
           'v_products_description_1',
           'v_products_price',
           'v_products_quantity',
           'v_categories_name_1',
           'v_products_small_description',
        ])
        q=self.db.query(ProductExt).filter(ProductExt.id <16801).filter(ProductExt.page==0)
        #q=self.db.query(ProductExt).filter(ProductExt.id ==12090)
        data=[]
        for r in q:
            description_m=""
            images=''
            if r.images=="":
                continue
            if "|x|" in r.images:
                images=r.images.strip('|x|')
                img=images.rsplit('|x|')
                newimg=''
                for i in img:
                    if i=="":
                        continue
                    newimg=newimg+i.rsplit('|:|')[1]+','
                images=newimg.strip(',')
            else:
                images=r.images
            catname=self.catname(r.cat_code)
            description_m=r.description_m.strip('<div class="description_m">').strip('</div>').replace( "　",'  ').replace("×",' X ').replace("＜",' < ')
            re_style=re.compile('style="[^"]*"',re.I)
            re_class=re.compile('class="[^"]*"',re.I)
            re_lang=re.compile('lang="[^"]*"',re.I)
            re_op=re.compile('<\s*o:p[^>]*>[^<]*<\s*/\s*o:p\s*>',re.I)
            description_m=re_style.sub('',description_m)
            description_m=re_class.sub('',description_m)
            description_m=re_lang.sub('',description_m)
            description_m=re_op.sub('',description_m)
            temp=(r.id,images,r.name,description_m,r.price,0,catname,r.summary)
            data.append(temp)
        writer.writerows(data)
        csvfile.close()
        csvfile = file('csv30000.csv', 'wb')
        writer = csv.writer(csvfile)
        writer.writerow([
           'v_products_model',
           'v_products_image',
           'v_products_name_1',
           'v_products_description_1',
           'v_products_price',
           'v_products_quantity',
           'v_categories_name_1',
           'v_products_small_description',
        ])
        q=self.db.query(ProductExt).filter(ProductExt.id >16800).filter(ProductExt.id <30001).filter(ProductExt.page==0)
        data=[]
        for r in q:
            description_m=""
            images=''
            if r.images=="":
                continue
            if "|x|" in r.images:
                images=r.images.strip('|x|')
                img=images.rsplit('|x|')
                newimg=''
                for i in img:
                    if i=="":
                        continue
                    newimg=newimg+i.rsplit('|:|')[1]+','
                images=newimg.strip(',')
            else:
                images=r.images
            catname=self.catname(r.cat_code)
            description_m=r.description_m.strip('<div class="description_m">').strip('</div>').replace( "　",'  ').replace("×",' X ')
            re_style=re.compile('style="[^"]*"',re.I)
            re_class=re.compile('class="[^"]*"',re.I)
            re_lang=re.compile('lang="[^"]*"',re.I)
            re_op=re.compile('<\s*o:p[^>]*>[^<]*<\s*/\s*o:p\s*>',re.I)
            description_m=re_style.sub('',description_m)
            description_m=re_class.sub('',description_m)
            description_m=re_lang.sub('',description_m)
            description_m=re_op.sub('',description_m)
            temp=(r.id,images,r.name,description_m,r.price,0,catname,r.summary)
            data.append(temp)
        writer.writerows(data)
        csvfile.close()
        csvfile = file('csv50000.csv', 'wb')
        writer = csv.writer(csvfile)
        writer.writerow([
           'v_products_model',
           'v_products_image',
           'v_products_name_1',
           'v_products_description_1',
           'v_products_price',
           'v_products_quantity',
           'v_categories_name_1',
           'v_products_small_description',
        ])
        q=self.db.query(ProductExt).filter(ProductExt.id >30000).filter(ProductExt.id <50001).filter(ProductExt.page==0)
        data=[]
        for r in q:
            description_m=""
            images=''
            if r.images=="":
                continue
            if "|x|" in r.images:
                images=r.images.strip('|x|')
                img=images.rsplit('|x|')
                newimg=''
                for i in img:
                    if i=="":
                        continue
                    newimg=newimg+i.rsplit('|:|')[1]+','
                images=newimg.strip(',')
            else:
                images=r.images
            catname=self.catname(r.cat_code)
            description_m=r.description_m.strip('<div class="description_m">').strip('</div>').replace( "　",'  ').replace("×",' X ')
            re_style=re.compile('style="[^"]*"',re.I)
            re_class=re.compile('class="[^"]*"',re.I)
            re_lang=re.compile('lang="[^"]*"',re.I)
            re_op=re.compile('<\s*o:p[^>]*>[^<]*<\s*/\s*o:p\s*>',re.I)
            description_m=re_style.sub('',description_m)
            description_m=re_class.sub('',description_m)
            description_m=re_lang.sub('',description_m)
            description_m=re_op.sub('',description_m)
            temp=(r.id,images,r.name,description_m,r.price,0,catname,r.summary)
            data.append(temp)
        writer.writerows(data)
        csvfile.close()
        csvfile = file('csv60000.csv', 'wb')
        writer = csv.writer(csvfile)
        writer.writerow([
           'v_products_model',
           'v_products_image',
           'v_products_name_1',
           'v_products_description_1',
           'v_products_price',
           'v_products_quantity',
           'v_categories_name_1',
           'v_products_small_description',
        ])
        q=self.db.query(ProductExt).filter(ProductExt.id >50000).filter(ProductExt.page==0)
        data=[]
        for r in q:
            description_m=""
            images=''
            if r.images=="":
                continue
            if "|x|" in r.images:
                images=r.images.strip('|x|')
                img=images.rsplit('|x|')
                newimg=''
                for i in img:
                    if i=="":
                        continue
                    newimg=newimg+i.rsplit('|:|')[1]+','
                images=newimg.strip(',')
            else:
                images=r.images
            catname=self.catname(r.cat_code)
            description_m=r.description_m.strip('<div class="description_m">').strip('</div>').replace( "　",'  ').replace("×",' X ')
            re_style=re.compile('style="[^"]*"',re.I)
            re_class=re.compile('class="[^"]*"',re.I)
            re_lang=re.compile('lang="[^"]*"',re.I)
            re_op=re.compile('<\s*o:p[^>]*>[^<]*<\s*/\s*o:p\s*>',re.I)
            description_m=re_style.sub('',description_m)
            description_m=re_class.sub('',description_m)
            description_m=re_lang.sub('',description_m)
            description_m=re_op.sub('',description_m)
            temp=(r.id,images,r.name,description_m,r.price,0,catname,r.summary)
            data.append(temp)
        writer.writerows(data)
        csvfile.close()
        # pem=ProductExt()
        # pem.name='name222'
        # pem.summary='summary22'
        # self.db.add(pem)
        # self.db.flush()
        # self.db.commit()

    def catname(self,cat_code):
        #cat_code='71C3EFA3CB8C7A9C088C018FEF386EEE'
        q=self.db.query(Category).filter(Category.url_code ==cat_code).first()
        return q.name

    def level(self):
        q=self.db.query(Category).filter(Category.level == 1)
        for r in q:
            r.pid=0
            r.reid=r.url_code
            self.db.commit()
        q=self.db.query(Category).filter(Category.level == 2)
        for r in q:
            pid= re.findall(r'\/ca-(\d{3})(\d{3})\.html',r.url)[0]
            r.pid=pid[0]
            r.reid=pid[1]
            self.db.commit()
        q=self.db.query(Category).filter(Category.level == 3)
        for r in q:
            pid= re.findall(r'\/ca-(\d{3})(\d{3})(\d{3})\.html',r.url)
            r.pid="%s,%s" % (pid[0][0],pid[0][1])
            r.reid=pid[0][2]
            self.db.commit()
            r.pid=pid
            self.db.commit()

    def notdub(self):
        q=self.db.query(Category).filter(Category.level == 3)
        for r in q:
            p=self.db.query(Product).filter(Product.cat_code== r.url_code)
            for pone in p:
                u=self.db.query(Product).filter(Product.url_code== pone.url_code).filter(Product.id<>pone.id)
                for i in u:
                    i.page=1
                    self.db.commit()
        sql=text("SELECT * FROM (SELECT COUNT(id) as ids,id,url_code FROM product where page is null GROUP BY url_code ORDER BY ids DESC) temp WHERE temp.ids>1")
        q = self.db.execute(sql).fetchall()
        # q=self.db.query(Product).group_by(Product.url_code).all()
        # print q
        for r in q:
            # break
            u=self.db.query(Product).filter(Product.url_code== r.url_code).filter(Product.id<>r.id)
            for i in u:
                i.page=1
                self.db.commit()
            #break


if __name__ == '__main__':
    pro=Image()
    # pro.level()
    pro.product()
    # pro.notdub()
    # pro.csv()
    # pro.test()
    # pro2.test2()